package c4.application.transactions.activities;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import c4.application.transactions.Activity;
import c4.application.transactions.ActivityResult;
import c4.application.transactions.CorporateDatabase;
import c4.application.transactions.Resource;
import c4.common.Constants;

public class CreateCustomerActivity extends Activity {

	public CreateCustomerActivity(Properties props) {
		super(props);
	}
	
	public ActivityResult execute(Resource db, Resource noss) {
		CorporateDatabase database = null;
		ActivityResult result= new ActivityResult();
		
		if(!(db instanceof CorporateDatabase)) {
			result.setResult(-1);
		}
		else {
			database = (CorporateDatabase)db;
			String query = "";
			boolean successful;
			boolean exists = false;
			ResultSet rs;
			
			
			//ensure account not already created for address (city, state, zip, street)
			query += 	"SELECT city, state, zip, street " +
						"FROM account_information " +
						"WHERE city=\"" + (String)parameters.getProperty(Constants.CITY) + "\" AND " +
						"state='" + (String)parameters.getProperty(Constants.STATE) + "' AND " +
						"zip='" + (String)parameters.getProperty(Constants.ZIP) + "' AND " +
						"street=\"" + (String)parameters.getProperty(Constants.STREET) + "\";";
			rs =database.executeQuery(query);
			
			try {
				if (rs.first()) {
					exists = true;
				}
			} 
			catch (SQLException e) {
			}
			
			if (! exists) {
				
				//insert into the database
				query = "";
				
				query += "INSERT INTO account_information (pin, name, dob, ssn, street, zip, city, state, voicemail, caller_id, three_way, distance_provider) VALUES (";
				query += "'" + (String)parameters.getProperty(Constants.PIN) + "'" + ",";
				query += "'" + (String)parameters.getProperty(Constants.NAME) + "'" + ",";
				query += "'" +(String)parameters.getProperty(Constants.DOB) + "'"+ ",";
				query += "'" +(String)parameters.getProperty(Constants.SSN) + "'"+ ",";
				query += "'" +(String)parameters.getProperty(Constants.STREET)+ "'" + ",";
				query += "'" +(String)parameters.getProperty(Constants.ZIP) + "'"+ ",";
				query += "'" +(String)parameters.getProperty(Constants.CITY)+ "'" + ",";
				query += "'" +(String)parameters.getProperty(Constants.STATE) + "'"+ ",";
				query += "'" +(String)parameters.getProperty(Constants.VOICEMAIL) + "'"+ ",";
				query += "'" +(String)parameters.getProperty(Constants.CALLER_ID)+ "'" + ",";
				query += "'" +(String)parameters.getProperty(Constants.THREE_WAY) + "'"+ ",";
				query += "'" +(String)parameters.getProperty(Constants.DISTANCE_PROVIDER) + "'"+ ");";

				successful = database.execute(query);
				
				if(successful) {
					
					//get the account number so that it can be returned- use the address to query
					query="";
					
					query += 	"SELECT account_number " +
								"FROM account_information " +
								"WHERE " +
								"city=\"" + (String)parameters.getProperty(Constants.CITY) + "\" AND " +
								"state='" + (String)parameters.getProperty(Constants.STATE) + "' AND " +
								"zip='" + (String)parameters.getProperty(Constants.ZIP) + "' AND " +
								"street=\"" + (String)parameters.getProperty(Constants.STREET) + "\";";
					
					rs = database.executeQuery(query);
					
					try {
						rs.first();
					
						String accountNumber = "";
						accountNumber = rs.getString("account_number");
						
						//create response
						result.addProperties(parameters);
						result.addProperty(Constants.ACCOUNT_NUMBER, accountNumber);
						
						//lock the account
						query = "";
						
						query += 	"INSERT INTO account_locks " +
									"VALUES (" + accountNumber + ", \"Customer already logged in for this account\");";
						
						database.execute(query);
						
						result.setResult(1);
					}
					catch (SQLException sqle) {
						result.setResult(-1);
						result.addErrorMessage("Cannot locate customer's account number");
					}
				}
				else
					result.setResult(-1);
				
			}
			else {
				result.setResult(-1);
				result.addErrorMessage("Account with this address already exists");
			}
		}

		return result;
	}
	
	 
	public String getType() {
		return Constants.DATABASE;
	}
}
